package javabean.user;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javabean.DataBaseConnection;

public class UserDAOImpl implements UserDAO {
	//登陆验证
	public boolean isLogin(User user) throws Exception{
		boolean flag = false ;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm = null ;
		ResultSet rs = null ;
		String sql = "SELECT uname FROM users WHERE (uid=? OR email=?) AND password=?";
		try{
			pstm = dbc.getConnection().prepareStatement(sql);
			pstm.setString(1,user.getUid());
			pstm.setString(2,user.getUid());
			pstm.setString(3,user.getPassword());
			rs = pstm.executeQuery() ;
			if(rs.next()){
				flag = true ;
				user.setUname(rs.getString(1)) ;
			}
			rs.close();
			pstm.close();
		}
		catch(Exception e){
			throw new Exception("登陆数据库出错！"+e);
		}
		finally{
			dbc.close();
		}
		
		return flag ;
	}
	//注册
	public int register(User user) throws Exception {
		//记录状态：（1）用户已存在或（2）注册成功
		int status = 0;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm ;
		String sql = "INSERT INTO users(uid,password,email,pic) " +
				"VALUES(?,?,?,?)";
		if(!"".equals(this.queryNameByUidOrEmail(user.getUid()))){
			status = 1; 
		}
		if(status == 0){
			try{
				pstm = dbc.getConnection().prepareStatement(sql) ;
				pstm.setString(1, user.getUid());
				pstm.setString(2, user.getPassword());
				pstm.setString(3, user.getEmail());
				pstm.setString(4, user.getPic());
				pstm.executeUpdate() ;
				pstm.close() ;
				this.updateActivity(user);
				status = 2 ;
			}
			catch(Exception e){
				throw new Exception("注册数据库出错！"+e);
			}
			finally{
				dbc.close();
			}
		}	
		return status ;
	}
	
	/*public int register(User user) throws Exception {
		//记录状态：（1）用户已存在或（2）注册成功
		int status = 0;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm2 ;
		String sql = "INSERT INTO users(uid,uname,password,sex,grade,major,banbie,tel,shorttel,email,dormitory,introduce,birthday) " +
				"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		if(!"".equals(this.queryNameByUidOrEmail(user.getUid()))){
			status = 1; 
		}
		if(status == 0){
			try{
				pstm2 = dbc.getConnection().prepareStatement(sql) ;
				pstm2.setString(1, user.getUid());
				pstm2.setString(2, user.getUname());
				pstm2.setString(3, user.getPassword());
				pstm2.setString(4, user.getSex()) ;
				pstm2.setInt(5, user.getGrade()) ;
				pstm2.setString(6, user.getMajor()) ;
				pstm2.setInt(7, user.getBanbie()) ;
				pstm2.setLong(8, user.getTel()) ;
				pstm2.setInt(9, user.getShorttel()) ;
				pstm2.setString(10, user.getEmail());
				pstm2.setString(11, user.getDormitory());
				pstm2.setString(12, user.getIntroduce()) ;
				pstm2.setString(13, user.getBirthday()) ;
				pstm2.executeUpdate() ;
				status = 2 ;
				pstm2.close() ;
			}
			catch(Exception e){
				throw new Exception("注册数据库出错！"+e);
			}
			finally{
				dbc.close();
			}
		}	
		return status ;
	}*/
	
	//更新活跃度
	public boolean updateActivity(User user) throws Exception{
		boolean flag = false ;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm = null ,pstm1 = null,pstm2=null ;
		ResultSet rs = null ;
		
		String sql1 = "SELECT activity FROM user_level WHERE uid=?" ;
		String sql2 = "UPDATE user_level SET activity=? WHERE uid=?" ;
		String sql3 = "INSERT INTO user_level(uid,activity) VALUES(?,?)" ;
		try{
			//更新用户活跃度
			pstm = dbc.getConnection().prepareStatement(sql1) ;
			pstm.setString(1,user.getUid());
			rs = pstm.executeQuery() ;
			if(rs.next()){
				pstm1 = dbc.getConnection().prepareStatement(sql2);
				pstm1.setLong(1,rs.getLong(1)+1);
				pstm1.setString(2, user.getUid()) ;
				pstm1.executeUpdate() ;
				flag = true ;
				pstm1.close() ;
			}
			else{
				pstm2 = dbc.getConnection().prepareStatement(sql3) ;
				pstm2.setString(1,user.getUid());
				pstm2.setLong(2,1) ;
				pstm2.executeUpdate() ;
				flag = true ;
				pstm2.close() ;
			}
			rs.close() ;
		}
		catch(Exception e){
		}
		finally{
			dbc.close() ;
		}
		return flag;
		
	}
	
	//更新贡献值
	public boolean updateContribution(String uid) throws Exception{
		boolean flag = false ;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm = null ,pstm1 = null,pstm2=null ;
		ResultSet rs = null ;
		
		String sql1 = "SELECT contribution FROM user_level WHERE uid=?" ;
		String sql2 = "UPDATE user_level SET contribution=? WHERE uid=?" ;
		try{
			//更新用户贡献值
			pstm = dbc.getConnection().prepareStatement(sql1) ;
			pstm.setString(1,uid);
			rs = pstm.executeQuery() ;
			if(rs.next()){
				pstm1 = dbc.getConnection().prepareStatement(sql2);
				pstm1.setLong(1,rs.getLong(1)+1);
				pstm1.setString(2, uid) ;
				pstm1.executeUpdate() ;
				flag = true ;
				pstm1.close() ;
			}
			rs.close() ;
		}
		catch(Exception e){
		}
		finally{
			dbc.close() ;
		}
		return flag;
		
	}
	
	//根据uid查uname
	public String queryNameByUidOrEmail(String key) throws Exception{
		String uname = "" ;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm = null ;
		ResultSet rs = null ;
		String sql = "SELECT uname FROM users WHERE uid=? OR email=?";
		try{
			pstm = dbc.getConnection().prepareStatement(sql);
			pstm.setString(1,key);
			pstm.setString(2,key);
			rs = pstm.executeQuery() ;
			if(rs.next()){
				uname = rs.getString(1) ;
			}
			rs.close();
			pstm.close();
		}
		catch(Exception e){
			throw new Exception("数据库出错！"+e);
		}
		finally{
			dbc.close();
		}
		return uname ;
	}
	//根据uid查用户所有信息
	public List<User> queryAllByUidOrEmail(String key) throws Exception{
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm = null ;
		ResultSet rs = null ;
		String sql = "SELECT * FROM users,user_level WHERE (users.uid=? OR users.email=?) AND users.uid=user_level.uid";
		List<User> all = new ArrayList<User>();
		User user = new User();
		try{
			pstm = dbc.getConnection().prepareStatement(sql);
			pstm.setString(1,key);
			pstm.setString(2,key);
			rs = pstm.executeQuery() ;
			if(rs.next()){
				user.setUid(rs.getString(1));
				user.setUname(rs.getString(2));
				user.setSex(rs.getString(4));
				user.setGrade(rs.getString(5));
				user.setMajor(rs.getString(6));
				user.setBanbie(rs.getString(7));
				user.setTel(rs.getString(8));
				user.setShorttel(rs.getString(9));
				user.setEmail(rs.getString(10));
				user.setDormitory(rs.getString(11));
				user.setIntroduce(rs.getString(12));
				user.setBirthday(rs.getString(13));
				user.setRole(rs.getString(14));
				user.setPic(rs.getString(15));
				user.setCredit(rs.getLong(17));
				user.setActivity(rs.getLong(18));
				user.setContribution(rs.getLong(19));
				user.setFavorability(rs.getLong(20));
				all.add(user);
			}
			rs.close();
			pstm.close();
		}
		catch(Exception e){
			throw new Exception("数据库出错！"+e);
		}
		finally{
			dbc.close();
		}
		return all ;
	}
	
	//修改基本资料
	public boolean edit(User user) throws Exception{
		boolean isSuccess = false;
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm = null ;
		String sql = "UPDATE users SET uname=?,sex=?,grade=?,major=?,banbie=?,tel=?,shorttel=?,email=?,dormitory=?,introduce=?,birthday=?,pic=? WHERE uid=?";
		try{
			pstm = dbc.getConnection().prepareStatement(sql) ;
			pstm.setString(1, user.getUname());
			pstm.setString(2, user.getSex());
			pstm.setString(3, user.getGrade());
			pstm.setString(4, user.getMajor());
			pstm.setString(5, user.getBanbie());
			pstm.setString(6, user.getTel());
			pstm.setString(7, user.getShorttel());
			pstm.setString(8, user.getEmail());
			pstm.setString(9, user.getDormitory());
			pstm.setString(10, user.getIntroduce());
			pstm.setString(11, user.getBirthday());
			pstm.setString(12, user.getPic());
			pstm.setString(13, user.getUid());
			pstm.executeUpdate();
			pstm.close();
			isSuccess = true;
		}catch(Exception ex){
			System.out.println("修改用户基本资料数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return isSuccess;
	}
	
	//修改密码
	public int changePassword(String uid,String oldpsw,String psw) throws Exception{
		int tag = 0;	//1代表修改成功，2代表旧密码错误
		DataBaseConnection dbc = new DataBaseConnection();
		PreparedStatement pstm1 = null, pstm2 ;
		String sql1 = "SELECT * FROM users WHERE uid=? AND password=?";
		String sql2 = "UPDATE users SET password=? WHERE uid=?";
		ResultSet rs = null;
		try{
			pstm1 = dbc.getConnection().prepareStatement(sql1) ;
			pstm1.setString(1, uid);
			pstm1.setString(2, oldpsw);
			rs = pstm1.executeQuery();
			if(rs.next()){
				pstm2 = dbc.getConnection().prepareStatement(sql2);
				pstm2.setString(1,psw);
				pstm2.setString(2, uid);
				pstm2.executeUpdate();
				tag = 1;
			}else{
				tag = 2;
			}
		}catch(Exception ex){
			System.out.println("修改密码数据库出错");
		}
		finally{
			dbc.close();
		}
		return tag;
	}
}



